<?php

class ProductInfo {
  protected static $producPropertiesCache = array();
  public	  static $_taxCalculationMethod = PS_TAX_EXC;
  
 
  public static function getProducts($id_lang)
  {
    global $cookie;  
    
    if(preg_match("/^1.3.*/", _PS_VERSION_)){
      $taxCondition= 'LEFT JOIN `'._DB_PREFIX_.'tax` t ON t.`id_tax` = products.`id_tax`';
      }
      else {
      $taxCondition = 'LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (products.`id_tax_rules_group` = tr.`id_tax_rules_group`
        AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
        AND tr.`id_state` = 0)
        LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)';
      } 

$sql = 'SELECT products.`id_product`, 
products.`id_category_default` , 
cl.`name` AS category_default, 
products.`price` ,
products.`price` as original_price, 
products.`wholesale_price` ,
pl.`link_rewrite` , 
pl.`name` , 
pl.`description` , 
images.`id_image`,
t.`rate` 
FROM  `'._DB_PREFIX_.'product` products
LEFT JOIN  `'._DB_PREFIX_.'product_lang` pl ON ( products.`id_product` = pl.`id_product` ) 
LEFT JOIN  `'._DB_PREFIX_.'category_lang` cl ON ( products.`id_category_default` = cl.`id_category` ) 
LEFT JOIN  `'._DB_PREFIX_.'image` images ON ( products.`id_product` = images.`id_product` AND images.`cover` =1 )'.$taxCondition.' 
WHERE products.`active` =1
AND pl.`id_lang` ='.intval($id_lang).'
AND cl.`id_lang` ='.intval($id_lang).''; 
    
    $result = ProductInfo::getDbInstance()->ExecuteS($sql); 
    
    if (!$result)
    {
       $error_info = ProductInfo::getDbInstance()->getMsgError();
          if (!$error_info)
              return false;
          else
              return $error_info;
    }

    /* Modify SQL result */
    return ProductInfo::getProductsProperties($id_lang, $result);
  }
  
   public static function getProduct($id_lang, $id_product)
  {
    global $cookie;    
    
        if(preg_match("/^1.3.*/", _PS_VERSION_)){
      $taxCondition= 'LEFT JOIN `'._DB_PREFIX_.'tax` t ON t.`id_tax` = products.`id_tax`';
      }
      else {
      $taxCondition = 'LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (products.`id_tax_rules_group` = tr.`id_tax_rules_group`
        AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
        AND tr.`id_state` = 0)
        LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)';
      } 

$sql = 'SELECT products.`id_product` , 
products.`id_category_default` , 
cl.`name` AS category_default, 
products.`price` ,
products.`price` as original_price, 
products.`wholesale_price` , 
pl.`link_rewrite` , 
pl.`name` , 
pl.`description` , 
images.`id_image`,
t.`rate`  
FROM  `'._DB_PREFIX_.'product` products
LEFT JOIN  `'._DB_PREFIX_.'product_lang` pl ON ( products.`id_product` = pl.`id_product` ) 
LEFT JOIN  `'._DB_PREFIX_.'category_lang` cl ON ( products.`id_category_default` = cl.`id_category` ) 
LEFT JOIN  `'._DB_PREFIX_.'image` images ON ( products.`id_product` = images.`id_product` AND images.`cover` =1 )'.$taxCondition.' 
WHERE products.`active` =1
AND pl.`id_lang` ='.intval($id_lang).'
AND cl.`id_lang` ='.intval($id_lang).'
AND products.`id_product`='.intval($id_product).'';

    $result = ProductInfo::getDbInstance()->ExecuteS($sql); 
   
     if (!$result)
    {
       $error_info = ProductInfo::getDbInstance()->getMsgError();
          if (!$error_info)
              return false;
          else
              return $error_info;
    }

    /* Modify SQL result */
    return ProductInfo::getProductsProperties($id_lang, $result);
  }
  
  public static function getProductsToSync($id_lang, $daysCount)
{
    global $cookie;   

$sql = 'SELECT products.`id_product`, 
                   products.`id_category_default`,
                   cl.`name` AS category_default,				   
                   products.`price`, 
                   products.`wholesale_price`, 
                   pl.`link_rewrite`, 
                   pl.`name`, 
                   pl.`description`, 
                   images.`id_image`             
FROM `'._DB_PREFIX_.'product` products
LEFT JOIN  `'._DB_PREFIX_.'product_lang` pl ON ( products.`id_product` = pl.`id_product` ) 
LEFT JOIN  `'._DB_PREFIX_.'category_lang` cl ON ( products.`id_category_default` = cl.`id_category` ) 
LEFT JOIN  `'._DB_PREFIX_.'image` images ON ( products.`id_product` = images.`id_product` AND images.`cover` =1 ) 
WHERE products.`active` =1
AND pl.`id_lang` ='.intval($id_lang).'
AND cl.`id_lang` ='.intval($id_lang).'
AND DATEDIFF( products.`date_upd`, DATE_SUB(CURDATE(), INTERVAL '.(int)($daysCount).' DAY))>=0';  

    $result = ProductInfo::getDbInstance()->ExecuteS($sql); 
   
     if (!$result)
    {
       $error_info = ProductInfo::getDbInstance()->getMsgError();
          if (!$error_info)
              return false;
          else
              return $error_info;
    }

    /* Modify SQL result */
    return ProductInfo::getProductsProperties($id_lang, $result);
}


  
  public static function getProductsCount($id_lang)
  {  
  
  $result = ProductInfo::getDbInstance()->getRow('
  SELECT COUNT(*) AS total
FROM  `'._DB_PREFIX_.'product` products
LEFT JOIN  `'._DB_PREFIX_.'product_lang` pl ON ( products.`id_product` = pl.`id_product` ) 
LEFT JOIN  `'._DB_PREFIX_.'category_lang` cl ON ( products.`id_category_default` = cl.`id_category` ) 
LEFT JOIN  `'._DB_PREFIX_.'image` images ON ( products.`id_product` = images.`id_product` AND images.`cover` =1 ) 
WHERE products.`active` =1
AND pl.`id_lang` ='.intval($id_lang).'
AND cl.`id_lang` ='.intval($id_lang).'');   
  
return isset($result) ? $result['total'] : 0;
    
  }

 public static function getProductsProperties($id_lang, $query_result)
  {
    $resultsArray = array();
    foreach ($query_result AS $row)
      if ($row2 = ProductInfo::getProductProperties($id_lang, $row))
      $resultsArray[] = $row2;
    return $resultsArray;
  }

  public static function getProductProperties($id_lang, $row)
  {
     if (!$row['id_product'])
      return false;

    if (!isset($row['id_product_attribute']))
      $row['id_product_attribute'] = 0;

   
    $usetax = Tax::excludeTaxeOption();

    $cacheKey = $row['id_product'].'-'.$row['id_product_attribute'].'-'.$id_lang.'-'.(int)($usetax);
    if (array_key_exists($cacheKey, self::$producPropertiesCache))
      return self::$producPropertiesCache[$cacheKey];
  

    if(!preg_match("/^1.3.*/", _PS_VERSION_)){ 
      $row['reduction'] = Product::getPriceStatic((int)($row['id_product']), (bool)$usetax, (int)($row['id_product_attribute']), 6, NULL, true, true, 1, true, NULL, NULL, NULL, $specific_prices);
      if($specific_prices == false)
         $row['specific_prices'] = null;
      else      
        $row['specific_prices'] = $specific_prices;
    }

 $sql_categories = 'SELECT pc.`id_category`,
                           cl.`name` 
                       FROM `'._DB_PREFIX_.'category_product` pc 
                       LEFT JOIN `'._DB_PREFIX_.'category_lang` cl on pc.`id_category`= cl.`id_category`
                       WHERE `id_product`=' . $row['id_product'].' and cl.`id_lang`= '. $id_lang .'';

 $result_categories = ProductInfo::getDbInstance()->ExecuteS($sql_categories);
 $row['categories'] = Array();
 $assosiatedCategories['categories'] = Array();
  if($result_categories ){
      foreach ($result_categories as $category)
	  {
	  array_push($assosiatedCategories['categories'], $category);
	  }
	  array_push($row['categories'], $assosiatedCategories);
	  }


 $sql_image = '
      SELECT DISTINCT i.*, pl.link_rewrite
      FROM `'._DB_PREFIX_.'product` p
      LEFT JOIN `'._DB_PREFIX_.'image` i ON (p.`id_product` = i.`id_product`)
      LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product`)
      WHERE p.`id_product` =' . $row['id_product'];

    $result_image = ProductInfo::getDbInstance()->ExecuteS($sql_image); 
    $row['images'] = Array();
    $lang = new Language($id_lang);
    $row['url_locale'] = $lang->iso_code;
    $link = new Link();

    if($result_image){
      foreach ($result_image as $image){
        if(!preg_match("/^1.3.*/", _PS_VERSION_)){ 
          $image['image_url'] = $link->getImageLink($image['link_rewrite'], $image['id_product'].'-'.$image['id_image']);
        }
        array_push($row['images'], $image);
      }
    }

    self::$producPropertiesCache[$cacheKey] = $row;
    return self::$producPropertiesCache[$cacheKey];
  }
 
 
  public static function getProducts2($id_category, $id_product, $id_lang, $p, $n, $orderBy = NULL, $orderWay = NULL, $getTotal = false, $active = true, $random = false, $randomNumberProducts = 1)
  {
    global $cookie; 

	$id_supplier = (int)(Tools::getValue('id_supplier'));    

    if (isset($id_product)) {
      $where = 'p.`id_product` = '.(int)($id_product);
    }

    if(preg_match("/^1.3.*/", _PS_VERSION_)){
      $sql = '
      SELECT p.*, p.price as original_price, pa.`id_product_attribute`, pl.`description`, pl.`description_short`, pl.`available_now`, pl.`available_later`, pl.`link_rewrite`, pl.`meta_description`, pl.`meta_keywords`, pl.`meta_title`, pl.`name`, i.`id_image`, il.`legend`, m.`name` AS manufacturer_name, tl.`name` AS tax_name, t.`rate`, cl.`name` AS category_default, DATEDIFF(p.`date_add`, DATE_SUB(NOW(), INTERVAL '.(Validate::isUnsignedInt(Configuration::get('PS_NB_DAYS_NEW_PRODUCT')) ? Configuration::get('PS_NB_DAYS_NEW_PRODUCT') : 20).' DAY)) > 0 AS new,
        (p.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1) - IF((DATEDIFF(`reduction_from`, CURDATE()) <= 0 AND DATEDIFF(`reduction_to`, CURDATE()) >=0) OR `reduction_from` = `reduction_to`, IF(`reduction_price` > 0, `reduction_price`, (p.`price` * IF(t.`rate`,((100 + (t.`rate`))/100),1) * `reduction_percent` / 100)),0)) AS price 
      FROM `'._DB_PREFIX_.'category_product` cp
      LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
      LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
      LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.intval($id_lang).')
      LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.intval($id_lang).')
      LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
      LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.intval($id_lang).')
      LEFT JOIN `'._DB_PREFIX_.'tax` t ON t.`id_tax` = p.`id_tax`
      LEFT JOIN `'._DB_PREFIX_.'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = '.intval($id_lang).')
      LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
      WHERE '. $where .($active ? ' AND p.`active` = 1' : '').'
      '.($id_supplier ? 'AND p.id_supplier = '.$id_supplier : '');
    }else{
      $sql = '
        SELECT p.`price` as original_price, p.*, pl.`description`, pl.`description_short`, pl.`link_rewrite`, pl.`name`, t.`rate`
        FROM `'._DB_PREFIX_.'category_product` cp
        LEFT JOIN `'._DB_PREFIX_.'product` p ON p.`id_product` = cp.`id_product`
        LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (p.`id_product` = pa.`id_product` AND default_on = 1)
        LEFT JOIN `'._DB_PREFIX_.'category_lang` cl ON (p.`id_category_default` = cl.`id_category` AND cl.`id_lang` = '.(int)($id_lang).')
        LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product` AND pl.`id_lang` = '.(int)($id_lang).')
        LEFT JOIN `'._DB_PREFIX_.'image` i ON (i.`id_product` = p.`id_product` AND i.`cover` = 1)
        LEFT JOIN `'._DB_PREFIX_.'image_lang` il ON (i.`id_image` = il.`id_image` AND il.`id_lang` = '.(int)($id_lang).')
        LEFT JOIN `'._DB_PREFIX_.'tax_rule` tr ON (p.`id_tax_rules_group` = tr.`id_tax_rules_group`
        AND tr.`id_country` = '.(int)Country::getDefaultCountryId().'
        AND tr.`id_state` = 0)
        LEFT JOIN `'._DB_PREFIX_.'tax` t ON (t.`id_tax` = tr.`id_tax`)
        LEFT JOIN `'._DB_PREFIX_.'tax_lang` tl ON (t.`id_tax` = tl.`id_tax` AND tl.`id_lang` = '.(int)($id_lang).')
        LEFT JOIN `'._DB_PREFIX_.'manufacturer` m ON m.`id_manufacturer` = p.`id_manufacturer`
        WHERE '. $where .($active ? ' AND p.`active` = 1' : '').'
        '.($id_supplier ? 'AND p.id_supplier = '.(int)$id_supplier : '');
    }

    $result = ProductInfo ::getDbInstance()->ExecuteS($sql); 
   
 if (!$result)
    {
       $error_info = ProductInfo::getDbInstance()->getMsgError();
          if (!$error_info)
              return false;
          else
              return $error_info;
    }
    
    return ProductInfo ::getProductsProperties2($id_lang, $result);
  }
  
    public static function getProductsProperties2($id_lang, $query_result)
  {
    $resultsArray = array();
    foreach ($query_result AS $row)
      if ($row2 = ProductInfo ::getProductProperties2($id_lang, $row))
      $resultsArray[] = $row2;
    return $resultsArray;
  }  
  
   public static function getProductProperties2($id_lang, $row)
  {
    if (!$row['id_product'])
      return false;
   
    $row['allow_oosp'] = Product::isAvailableWhenOutOfStock($row['out_of_stock']);
    if ((!isset($row['id_product_attribute']) OR !$row['id_product_attribute'])
      AND ((isset($row['cache_default_attribute']) AND ($ipa_default = $row['cache_default_attribute']) !== NULL)
      OR ($ipa_default = Product::getDefaultAttribute($row['id_product'], !$row['allow_oosp'])))
      )
      $row['id_product_attribute'] = $ipa_default;

    if (!isset($row['id_product_attribute']))
      $row['id_product_attribute'] = 0;
  
    $usetax = Tax::excludeTaxeOption();

    $cacheKey = $row['id_product'].'-'.$row['id_product_attribute'].'-'.$id_lang.'-'.(int)($usetax);
    if (array_key_exists($cacheKey, self::$producPropertiesCache))
      return self::$producPropertiesCache[$cacheKey];
   
    $row['category'] = Category::getLinkRewrite((int)$row['id_category_default'], (int)($id_lang));

    if(!preg_match("/^1.3.*/", _PS_VERSION_)){ 
      $row['reduction'] = Product::getPriceStatic((int)($row['id_product']), (bool)$usetax, (int)($row['id_product_attribute']), 6, NULL, true, true, 1, true, NULL, NULL, NULL, $specific_prices);
      if($specific_prices == false)
         $row['specific_prices'] = null;
      else      
        $row['specific_prices'] = $specific_prices;
    }
    if ($row['id_product_attribute'])
    {
      $row['quantity_all_versions'] = $row['quantity'];
      $row['quantity'] = Product::getQuantity((int)$row['id_product'], $row['id_product_attribute'], isset($row['cache_is_pack']) ? $row['cache_is_pack'] : NULL);
    }
    $row['id_image'] = Product::defineProductImage($row, $id_lang);
    $row['features'] = Product::getFrontFeaturesStatic((int)$id_lang, $row['id_product']);
    
    $row['pack'] = (!isset($row['cache_is_pack']) ? Pack::isPack($row['id_product']) : (int)$row['cache_is_pack']);
    $row['packItems'] = $row['pack'] ? Pack::getItemTable($row['id_product'], $id_lang) : array();
    $row['nopackprice'] = $row['pack'] ? Pack::noPackPrice($row['id_product']) : 0;
    if ($row['pack'] AND !Pack::isInStock($row['id_product']))
      $row['quantity'] =  0;

    $sql_combination = '
      SELECT pa.*
      FROM `'._DB_PREFIX_.'product` p
      LEFT JOIN `'._DB_PREFIX_.'product_attribute` pa ON (p.`id_product` = pa.`id_product`)
      WHERE p.`id_product` =' . $row['id_product'];

    $result_combination = ProductInfo ::getDbInstance()->ExecuteS($sql_combination); 
    $row['combinations'] = Array();

    if($result_combination){
      foreach ($result_combination as $combination){
        $combination['attributes'] = Array();
        if(isset($combination['id_product_attribute'])){
          $sql_attribute = '
            SELECT pa.id_product_attribute, agl.id_attribute_group, al.name as name_value, agl.name as name_option
            FROM `'._DB_PREFIX_.'product_attribute` pa
            LEFT JOIN `'._DB_PREFIX_.'product_attribute_combination` pac ON (pac.`id_product_attribute` = pa.`id_product_attribute`)
            LEFT JOIN `'._DB_PREFIX_.'attribute` a ON (a.`id_attribute` = pac.`id_attribute`)
            LEFT JOIN `'._DB_PREFIX_.'attribute_lang` al ON (al.`id_attribute` = a.`id_attribute` AND al.`id_lang` = '. $id_lang .')
            LEFT JOIN `'._DB_PREFIX_.'attribute_group` ag ON (ag.`id_attribute_group` = a.`id_attribute_group`)
            LEFT JOIN `'._DB_PREFIX_.'attribute_group_lang` agl ON (agl.`id_attribute_group` = ag.`id_attribute_group` AND agl.`id_lang` = '. $id_lang .')
            WHERE pa.`id_product_attribute` =' . $combination['id_product_attribute'];

          $result_attribute = ProductInfo ::getDbInstance()->ExecuteS($sql_attribute); 
          
          if($result_attribute)
            foreach ($result_attribute as $attribute)
              array_push($combination['attributes'], $attribute);

          array_push($row['combinations'], $combination);
        }
      }
    }

    $sql_image = '
      SELECT DISTINCT i.*, pl.link_rewrite
      FROM `'._DB_PREFIX_.'product` p
      LEFT JOIN `'._DB_PREFIX_.'image` i ON (p.`id_product` = i.`id_product`)
      LEFT JOIN `'._DB_PREFIX_.'product_lang` pl ON (p.`id_product` = pl.`id_product`)
      WHERE p.`id_product` =' . $row['id_product'];

    $result_image = ProductInfo ::getDbInstance()->ExecuteS($sql_image); 
    $row['images'] = Array();
    $lang = new Language($id_lang);
    $row['url_locale'] = $lang->iso_code;
    $link = new Link();

    if($result_image){
      foreach ($result_image as $image){
        if(!preg_match("/^1.3.*/", _PS_VERSION_)){  
          $image['image_url'] = $link->getImageLink($image['link_rewrite'], $image['id_product'].'-'.$image['id_image']);
        }
        array_push($row['images'], $image);
      }
    }

    self::$producPropertiesCache[$cacheKey] = $row;
    return self::$producPropertiesCache[$cacheKey];
  }

  public static function getDbInstance(){
    if(preg_match("/^1.3.*/", _PS_VERSION_)){
      return Db::getInstance();
    }else{
      return Db::getInstance(_PS_USE_SQL_SLAVE_);
    }
  }
}